const query = require('../../dbConfig/index')
const XLSX = require('xlsx'); 
const fs = require('fs');
const path = require('path');
const {atDate,mapTxt,generateUUID} = require('../../utils/utils')
const state = {"0":"在校","1":"放假","2":"请假","3":"就业","4":"实习","5":"退学","6":"停课","7":"休学"}
const get = async (ctx,next)=>{

	console.log(ctx.state)
	const body = ctx.request.body
	const offset = (body.pageNumber - 1) * Number(body.pageSize);  
	let sql = 'SELECT * FROM student';  
	let params = [];  
	let pageParams=[]
	// 如果传了条件 就拼接sql
	if (body.studentNum) {  
		sql += sql.includes('WHERE')? ' and student_num=?': ' WHERE student_num=?';  
		pageParams.push(`student_num='${body.studentNum}'`)
		params.push(body.studentNum);  
	}
	if (body.studentName) {  
		sql += sql.includes('WHERE')?  'and student_name=?':' WHERE student_name=?';  
		pageParams.push(`student_name='${body.studentName}'`)
		params.push(body.studentName);  
	}
	if (body.studentSex) {  
		sql +=sql.includes('WHERE')? ' and student_sex=?': ' WHERE student_sex=?';  
		pageParams.push(`student_sex='${body.studentSex}'`)
		params.push(body.studentSex);  
	}
	if (body.studentAge) {  
		sql +=sql.includes('WHERE')? ' and student_age=?': ' WHERE student_age=?';  
		pageParams.push(`student_age='${body.studentAge}'`)
		params.push(body.studentAge);  
	}
	if (body.cid) {  
		sql += sql.includes('WHERE')? ' and classes_id=?':' WHERE classes_id=?';  
		pageParams.push(`classes_id=${body.cid}`)
		params.push(body.cid);  
	}else if(ctx.state.power!=1&&ctx.state.classes){
		let str = ctx.state.classes.split(",").map(num => parseInt(num))
		sql += sql.includes('WHERE')? ' and classes_id IN (?)':' WHERE classes_id IN (?)';  
		pageParams.push(`classes_id IN (${str})`)
		params.push(str); 
	}
	if (body.schoolYear) {  
		sql +=sql.includes('WHERE')? 'and school_year=?': ' WHERE school_year=?';  
		pageParams.push(`school_year='${body.schoolYear}'`)
		params.push(body.schoolYear);  
	}
	if (body.specialty) {  
		sql +=sql.includes('WHERE')? 'and specialty=?': ' WHERE specialty=?';  
		pageParams.push(`specialty=${body.specialty}`)
		params.push(body.specialty);  
	}  
	  sql += ' LIMIT ? OFFSET ?';  
	  params.push(Number(body.pageSize));  
	  params.push(offset);
	const res = await query(sql,params)
	console.log(pageParams)
	const total = await query(`SELECT COUNT(*) as total FROM student ${pageParams.length ?('where ' + pageParams.join(' and ')):''}`)
	if(res.length>0){
		ctx.body={code:200,data:{records:res,total:total[0].total},msg:'成功'}
		next()
	}else{
		ctx.body={code:0,data:null,msg:'暂无学生数据'}
	}
}
const getItem = async (ctx,next)=>{
	const body = ctx.request.body  
	let sql = `SELECT s.*, c.*, p.* FROM student s LEFT JOIN classes c ON s.classes_id = c.classes_id LEFT JOIN speciality p ON s.specialty = p.speciality_id WHERE s.student_id = '${body.student_id}'`
	const res = await query(sql)
	if(res.length>0){
		ctx.body={code:200,data:res,msg:'成功'}
		next()
	}else{
		ctx.body={code:0,data:null,msg:'查无此人'}
	}
}
const getStudentHistery = async (ctx,next)=>{
	const body = ctx.request.body  
	let sql = `SELECT * FROM student_histery WHERE student_id = '${body.student_id}' ORDER BY change_date ASC`
	const res = await query(sql)
	if(res.length>0){
		ctx.body={code:200,data:res,msg:'成功'}
		next()
	}else{
		ctx.body={code:0,data:null,msg:'暂无数据'}
	}
}
const updata = async (ctx,next)=>{
	const body = ctx.request.body
	// 查询未修改之前的  和 即将要修改的做比较 找出修改项
	const atStudent = await query(`select * from student WHERE student_id='${body.student_id}'`)
	const classesList = await query(`select * from classes`)
	const specialtyList = await query(`select * from speciality`)
	await query(`UPDATE student SET student_num = ?,student_name=?,student_sex=?,student_age=?,qualification=?,school_year=?,student_nation=?,student_birthday = ?,classes_id=?,start_school_time=?,school_status=?,drop_time=?,address=?,graduate_time = ?,student_tel=?,specialty=?,remarks=?,hobby=?,family=? WHERE student_id='${body.student_id}'`,[body.student_num,body.student_name,body.student_sex,body.student_age,body.qualification,body.school_year,body.student_nation,body.student_birthday,body.classes_id,body.start_school_time,body.school_status,body.drop_time,body.address,body.graduate_time,body.student_tel,body.specialty,body.remarks,body.hobby,body.family])
	let content=''
	if(Number(atStudent[0].classes_id) !== Number(body.classes_id)){ // 调班了
		content=`[调班]原班级:${mapTxt({source:classesList,key:'classes_id',target:atStudent[0].classes_id,label:'classes_name'})} 调至 ${mapTxt({source:classesList,key:'classes_id',target:body.classes_id,label:'classes_name'})}` 
		await query(`INSERT INTO student_histery (student_id,change_date,change_txt,remakes) VALUES (?,?,?,?)`,[body.student_id,atDate(),content,body.remarks])
	}
	if(atStudent[0].school_status != body.school_status){ // 状态变了
		content=`[状态]由:${state[atStudent[0].school_status]} 变为 ${state[body.school_status]}`
		await query(`INSERT INTO student_histery (student_id,change_date,change_txt,remakes) VALUES (?,?,?,?)`,[body.student_id,atDate(),content,body.remarks])
	}
	if(atStudent[0].specialty != body.specialty){ // 专业变了
		const source = atStudent[0].specialty?specialtyList:'' // 考虑到刚入学的学生可能未选择专业
		content=`[专业]由:${mapTxt({source,key:'speciality_id',target:atStudent[0].specialty,label:'speciality_name'})} 调至 ${mapTxt({source:specialtyList,key:'speciality_id',target:body.specialty,label:'speciality_name'})}` 
		await query(`INSERT INTO student_histery (student_id,change_date,change_txt,remakes) VALUES (?,?,?,?)`,[body.student_id,atDate(),content,body.remarks])
	}
	ctx.body={code:200,data:null,msg:'成功'}
	next()
}
const add = async (ctx,next)=>{
	const body = ctx.request.body
	const uuid = generateUUID()
	await query(`INSERT INTO student (uuid,student_num,student_name,student_sex,student_age,qualification,school_year,student_nation,student_birthday,classes_id,start_school_time,school_status,drop_time,address,graduate_time,student_tel,specialty,remarks,hobby,family) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,[uuid,body.student_num,body.student_name,body.student_sex,body.student_age,body.qualification,body.school_year,body.student_nation,body.student_birthday,body.classes_id,body.start_school_time,body.school_status,body.drop_time,body.address,body.graduate_time,body.student_tel,body.specialty||null,body.remarks,body.hobby,body.family])
	const filePath = path.join(__dirname, `../../sudentTaskFile/${uuid}${body.student_name}.txt`);
	fs.writeFileSync(filePath, '', 'utf8');
	ctx.body={code:200,data:null,msg:'成功'}
	next()
}
const remove = async (ctx,next)=>{
	// TODO  删除和学生相关的所有数据
	const body = ctx.request.body
	await query(`DELETE FROM student WHERE student_id=${body.id}`)
	await query(`DELETE FROM attendance WHERE student_id=${body.id}`)
	await query(`DELETE FROM parent WHERE student_id=${body.id}`)
	await query(`DELETE FROM performance WHERE student_id=${body.id}`)
	await query(`DELETE FROM rewards WHERE student_id=${body.id}`)
	await query(`DELETE FROM student_classes WHERE student_id=${body.id}`)
	await query(`DELETE FROM student_histery WHERE student_id=${body.id}`)
	await query(`DELETE FROM transcript WHERE student_id=${body.id}`)
	const filePath = path.join(__dirname, `../../sudentTaskFile/${body.uuid}${body.student_name}.txt`);
	await fs.rm(filePath,(err)=>{console.log(err)});
	ctx.body={code:200,data:null,msg:'成功'}
	next()
}
const exportFile = async (ctx,next)=>{
	const res = await query(`select * FROM student`)
	const speciality = await query(`select * FROM speciality`)
	const stateMap = {0:'在校',1:'放假',2:'请假',3:'就业',4:'实习',5:'退学',6:'停课',7:'休学'}
	const obj = {}
	speciality.forEach(item=>{
		obj[item.speciality_id] = item.speciality_name
	})
	res.forEach(element => {
		element.specialty = obj[element.specialty]
		element.student_sex = Number(element.student_sex)?'女':'男'
		element.school_status = stateMap[element.school_status]
	});
	const wb = XLSX.utils.book_new();  
	  const ws = XLSX.utils.json_to_sheet(res);  
	  const headers = ['序号', '学号', '姓名','性别','年龄','学制','民族','出生年月','班级','入学时间','状态','退学时间','地址','毕业时间','手机号','专业'];
	  const col = ['A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1','M1','N1','O1','P1']
	  for(let i=0;i<col.length;i++){
		ws[col[i]] = { t: 's', v: headers[i] };
	  }
	  XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
	  const wbout = XLSX.write(wb, { bookType: 'xlsx', type: 'buffer' }); 
	  ctx.set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');  
  	  ctx.set('Content-Disposition', 'attachment; filename="data.xlsx"');
	  ctx.body = wbout;
	next()
}
const importFile = async (ctx,next)=>{
	// console.log(ctx.request.files.studentsExcel.filepath)
	const filePath = ctx.request.files.studentsExcel.filepath; 
	const workbook = XLSX.readFile(filePath);
	const worksheet = workbook.Sheets[workbook.SheetNames[0]]; 
	const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 2 }); 
	jsonData.shift()
	for (let i = 0; i < jsonData.length; i++) {
		delete jsonData[i]['学生学籍信息']
		delete jsonData[i]['_14']
	}
	console.log(jsonData,1)
	// TODO 可以用数组试试
	let values = ''
	for(let i=0;i<jsonData.length;i++){
			// values+=`(${jsonData[i][""]},${jsonData[i]['_1']},${jsonData[i]['_2']},${jsonData[i]['_3']},${jsonData[i]['_4']},${jsonData[i]['_5']},${jsonData[i]['_6']},${jsonData[i]['_7']},${jsonData[i]['_8']},${jsonData[i]['_9']},${jsonData[i]['_10']||null},${jsonData[i]['_11']},${jsonData[i]['_12']},${jsonData[i]['_13']},${jsonData[i]['__EMPTY']})`
			await query(`INSERT INTO student (student_num,student_name,student_sex,student_age,school_year,student_nation,student_birthday,classes_id,start_school_time,school_status,drop_time,address,graduate_time,student_tel,specialty) VALUES ('${jsonData[i][""]}','${jsonData[i]['_1']}','${jsonData[i]['_2']}','${jsonData[i]['_3']}','${jsonData[i]['_4']}','${jsonData[i]['_5']}','${jsonData[i]['_6']}','${jsonData[i]['_7']}','${jsonData[i]['_8']}','${jsonData[i]['_9']}',${handelEmpty(jsonData[i]['_10'])},'${jsonData[i]['_11']}','${jsonData[i]['_12']}','${jsonData[i]['_13']}','${jsonData[i]['__EMPTY']}')`)
		}
	function handelEmpty(d){
		if(d){
			return "'"+d+"'"
		}else{
			return null
		}
	}
	ctx.body={code:200,data:null,msg:'成功'}
	next()
}
const addTask = async (ctx,next)=>{
	console.log(ctx.request.body)
	const body = ctx.request.body
	const info = {
        time: body.taskDate.split('T')[0],
        describe: body.taskContent,
        register:body.teacher
    };
	const logFilePath = path.join(__dirname, `../../sudentTaskFile/${body.taskTargetId}${body.taskTargetStudent}.txt`);
	fs.appendFileSync(logFilePath, JSON.stringify(info) + '\n');
	ctx.body={code:200,data:null,msg:'成功'}
	next()
}
const getTaskInfo = async (ctx,next)=>{
	let sql = `SELECT uuid,student_name FROM student where student_id=${ctx.request.body.student_id}`;
	const res = await query(sql)  
	if(res.length){
		const filePath =  path.join(__dirname, `../../sudentTaskFile/${res[0].uuid}${res[0].student_name}.txt`)
		const fileContent = fs.readFileSync(filePath, 'utf8');
		// console.log(JSON.parse(JSON.stringify(fileContent)))
		ctx.body={code:200,data:[fileContent],msg:'成功'}
		next()
	}
}
module.exports = {
	get,getItem,getStudentHistery,addTask,getTaskInfo,
	add,updata,remove,
	exportFile,
	importFile
}